import logging

import openpyxl
import datetime
import os
import sys
import configparser
import json
import hashlib

# 创建logger对象
logger = logging.getLogger('test_logger')
# 设置日志等级
logger.setLevel(logging.DEBUG)
# 追加写入文件a ，设置utf-8编码防止中文写入乱码
test_log = logging.FileHandler('autoCount.log', 'a', encoding='utf-8')
# 向文件输出的日志级别
test_log.setLevel(logging.DEBUG)
# 向文件输出的日志信息格式
formatter = logging.Formatter('%(asctime)s [%(levelname)s]  %(filename)s:%(lineno)d:(funcName)s %(message)s')
test_log.setFormatter(formatter)
# 加载文件到logger对象中
logger.addHandler(test_log)
log = logging.getLogger()
stdout_handler = logging.StreamHandler(sys.stdout)
log.addHandler(stdout_handler)

dirDiaoDuHuiZong = '\\\\172.16.10.81\\共享文件\\数据组\\调度工单汇总'
dirPath = 'E:\\日常工作\\数据组日常工-韩冲\\每日片区接单量统计'
# dirPath = os.path.dirname(os.path.realpath(sys.executable))
logger.debug('配置文件目录：{}'.format(dirPath))

timeCur = datetime.datetime.now()
iniFilePathCur = dirPath + '\\autoCount.ini'
iniConfig = configparser.ConfigParser(allow_no_value=True)
iniConfig.read(iniFilePathCur, encoding='utf-8')


def get_config_value(ini_config, section, section_key, value_default):
    sectionValue = value_default
    if ini_config.has_section(section):
        if ini_config.has_option(section, section_key):
            sectionValue = ini_config.get(section, section_key, )
            if sectionValue is not None and sectionValue != '':
                sectionValue = '{}'.format(sectionValue)
    return sectionValue


# 是否自定义工作目录
customizeWorkFolder = get_config_value(iniConfig, 'global', 'isCustomizeWorkFolder', '0')
if customizeWorkFolder != '0':
    workFolder = get_config_value(iniConfig, 'global', 'workFolder', dirPath)
    if workFolder != '' and dirPath != workFolder:
        if os.path.exists(workFolder):
            dirPath = workFolder
            logger.debug('是否自定义工作目录为：{}'.format(dirPath))
        else:
            logger.debug('自定义的工作目录不存在，工作目录：{}'.format(dirPath))
    else:
        logger.debug('工作目录未填写或者为空，或者与默认值相同，使用默认值：{}'.format(dirPath))
else:
    logger.debug('不使用自定义的工作目录，使用默认值:{}'.format(dirPath))

# 是否查询未解决工单的上一次调度日期
isSaveUndetermined = get_config_value(iniConfig, 'global', 'isSaveUndetermined', '1')
if isSaveUndetermined == '2':
    dirDiaoDuHuiZong = dirPath
    logger.debug('使用当前文件夹为未解决列表目录')

# 未解决工单汇总文件目录
if isSaveUndetermined == '1' or isSaveUndetermined == '3':
    OrderCountFolder = get_config_value(iniConfig, 'global', 'OrderCountFolder', dirDiaoDuHuiZong)
    if OrderCountFolder is not None:
        if isSaveUndetermined == '3':
            dirDiaoDuHuiZong = dirPath + OrderCountFolder
            logger.debug('未解决工单汇总文件目录：{}'.format(dirDiaoDuHuiZong))
        if os.path.exists(OrderCountFolder):
            if isSaveUndetermined == '1':
                dirDiaoDuHuiZong = OrderCountFolder
                logger.debug('未解决工单汇总文件目录：{}'.format(dirDiaoDuHuiZong))
        else:
            logger.debug('未解决工单汇总文件目录不存在，使用默认值，：{}'.format(dirDiaoDuHuiZong))

logger.debug('是否保存未解决工单:{} ，未解决工单汇总目录：{}'.format(isSaveUndetermined, dirDiaoDuHuiZong))
# 是否处理未处理工单列表
isProcessUnresolved = get_config_value(iniConfig, 'global', 'isProcessUnresolved', '1')
if isProcessUnresolved == '0':
    isProcessUnresolved = False
    logger.debug('不处理未处理工单列表，isProcessUnresolved = False')
else:
    isProcessUnresolved = True
    logger.debug('处理未处理工单列表，isProcessUnresolved = True')

# 是否处理接单量列表
isProcessReceiving = get_config_value(iniConfig, 'global', 'isProcessReceiving', '1')
if isProcessReceiving == '0':
    isProcessReceiving = False
    logger.debug('不处理处理接单量列表，isProcessReceiving = False')
else:
    isProcessReceiving = True
    logger.debug('处理接单量列表，isProcessReceiving = True')

dirPathOut = dirPath + '\\Out\\大屏'
dirPathWeiChuLi = dirPath + '\\未处理'
fileWorkOrderToday = dirPath + '\\今日未解决工单明细.xlsx'      # 生成的今日工单明细
fileWorkOrderTodayOut = dirPathWeiChuLi + '\\今日工单明细.xlsx'      # 生成的今日工单明细
fileWorkOrderTodayOutNo = dirPathWeiChuLi + '\\今日工单明细-未处理.xlsx'      # 生成的今日工单明细
fileWorkOrderDispatch = dirPath + '\\派单组-每日台账明细\\全部工单.xlsx'  # 派单组上传的今日派单情况
fileWorkOrderDispatchOut = dirPathWeiChuLi + '\\派单组-每日台账明细-全部工单.xlsx'  # 派单组上传的今日派单情况
fileWorkOrderDispatchOutNo = dirPathWeiChuLi + '\\派单组-每日台账明细-全部工单-未处理.xlsx'  # 派单组上传的今日派单情况
fileWorkOrderEast = dirPath + '\\[内部] 拱辰街道东部片区接单量及解决情况.xlsx'     # 拱辰街道东部片区接单量及解决情况
fileWorkOrderEastOut = dirPath + '\\Out\\[内部] 拱辰街道东部片区接单量及解决情况.xlsx'     # 拱辰街道东部片区接单量及解决情况
titleWorkOrderEast = '拱辰街道东部片区{}月{}日接单量及解决情况统计表'.format(timeCur.month, timeCur.day)
fileWorkOrderNorth = dirPath + '\\[内部] 拱辰街道北部片区接单量及解决情况.xlsx'    # 拱辰街道北部片区接单量及解决情况
fileWorkOrderNorthOut = dirPath + '\\Out\\[内部] 拱辰街道北部片区接单量及解决情况.xlsx'    # 拱辰街道北部片区接单量及解决情况
titleWorkOrderNorth = '拱辰街道北部片区{}月{}日接单量及解决情况统计表'.format(timeCur.month, timeCur.day)
fileWorkOrderSouthWest = dirPath + '\\[内部] 拱辰街道西南片区接单量及解决情况.xlsx'    # 拱辰街道西南片区接单量及解决情况
fileWorkOrderSouthWestOut = dirPath + '\\Out\\[内部] 拱辰街道西南片区接单量及解决情况.xlsx'    # 拱辰街道西南片区接单量及解决情况
titleWorkOrderSouthWest = '拱辰街道西南片区{}月{}日接单量及解决情况统计表'.format(timeCur.month, timeCur.day)
fileWorkOrderCentral = dirPath + '\\[内部] 拱辰街道中部片区接单量及解决情况.xlsx'    # 拱辰街道中部片区接单量及解决情况
fileWorkOrderCentralOut = dirPath + '\\Out\\[内部] 拱辰街道中部片区接单量及解决情况.xlsx'    # 拱辰街道中部片区接单量及解决情况
titleWorkOrderCentral = '拱辰街道中部片区{}月{}日接单量及解决情况统计表'.format(timeCur.month, timeCur.day)
fileWorkOrderKeShi = dirPath + '\\[内部] 拱辰街道科室接单量及解决情况.xlsx'    # 拱辰街道中部片区接单量及解决情况
fileWorkOrderKeShiOut = dirPath + '\\Out\\[内部] 拱辰街道科室接单量及解决情况.xlsx'    # 拱辰街道中部片区接单量及解决情况
titleWorkOrderKeShi = '拱辰街道科室{}月{}日接单量及解决情况统计表'.format(timeCur.month, timeCur.day)

# 创建输出文件夹
if os.path.exists(dirPathOut) is False:
    os.makedirs(dirPathOut)

# 大屏处理
# 拱辰街道北部片区接单量及解决情况统计表
fileWorkOrderNorthDaPing = dirPath + '\\大屏\\[内部] 拱辰街道北部片区接单量及解决情况统计表.xlsx'
fileWorkOrderNorthDaPingOut = dirPath + '\\Out\\大屏\\[内部] 拱辰街道北部片区接单量及解决情况统计表.xlsx'
# 拱辰街道东部片区接单量及解决情况统计表
fileWorkOrderEastDaPing = dirPath + '\\大屏\\[内部] 拱辰街道东部片区接单量及解决情况统计表.xlsx'
fileWorkOrderEastDaPingOut = dirPath + '\\Out\\大屏\\[内部] 拱辰街道东部片区接单量及解决情况统计表.xlsx'
# 拱辰街道西南片区接单量及解决情况统计表
fileWorkOrderSouthWestDaPing = dirPath + '\\大屏\\[内部] 拱辰街道西南片区接单量及解决情况统计表.xlsx'
fileWorkOrderSouthWestDaPingOut = dirPath + '\\Out\\大屏\\[内部] 拱辰街道西南片区接单量及解决情况统计表.xlsx'
# 拱辰街道中部片区接单量及解决情况
fileWorkOrderCentralDaPing = dirPath + '\\大屏\\[内部] 拱辰街道中部片区接单量及解决情况统计表.xlsx'
fileWorkOrderCentralDaPingOut = dirPath + '\\Out\\大屏\\[内部] 拱辰街道中部片区接单量及解决情况统计表.xlsx'
fileDiaoDuHuiZong = dirDiaoDuHuiZong
fileDiaoDuHuiZongLast = dirDiaoDuHuiZong
bookDiaoDuHuiZong = None
sheetDiaoDuHuiZong = None
fileDiaoDuHuiZongDir = None

# 计算本考核开始时间
timeKaoHe = datetime.datetime.strptime('2022-11-19', "%Y-%m-%d")
if timeCur.day < 19:
    if timeCur.month == 1:
        timeKaoHe = datetime.datetime(timeCur.year - 1, 12, 19, 0, 0, 0)
    else:
        timeKaoHe = datetime.datetime(timeCur.year, timeCur.month - 1, 19, 0, 0, 0)
else:
    timeKaoHe = datetime.datetime(timeCur.year, timeCur.month, 19, 0, 0, 0)
logger.debug('考核日期：{}-{}-{}'.format(timeKaoHe.year, timeKaoHe.month, timeKaoHe.day))

listTitle = ['序号', '工单编号', '片区', '主管领导', '承办部门', '参与部门', '标题', '主要内容', '来电人',
             '办理结果', '实际办理情况', '是否解决\n是否满意', '是否联系', '是否见面', '是否属实', '备注', '回访结果',
             '截止日期', '上一次调度日期', '调度日期']
listTitleConf = get_config_value(iniConfig, 'global', 'titleProcessList', '')
if listTitleConf != '':
    listTitle = json.loads(listTitleConf)
logger.debug('listTitle：{}'.format(listTitle))


def make_diao_du_file(time_set):
    global fileDiaoDuHuiZong
    global fileDiaoDuHuiZongLast
    global fileDiaoDuHuiZongDir
    fileDiaoDuHuiZongCur = fileDiaoDuHuiZong
    fileDiaoDuHuiZongDir = fileDiaoDuHuiZongCur + '\\{}'.format(time_set.year)
    if time_set.day < 19:
        fileDiaoDuHuiZong = fileDiaoDuHuiZongCur + '\\{}\\{}月调度工单汇总表.xlsx'.format(time_set.year, time_set.month)
        if time_set.month == 1:
            fileDiaoDuHuiZongLast = fileDiaoDuHuiZongCur + '\\{}\\12月调度工单汇总表.xlsx'.format(time_set.year - 1)
        else:
            fileDiaoDuHuiZongLast = fileDiaoDuHuiZongCur + '\\{}\\{}月调度工单汇总表.xlsx'.format(time_set.year, time_set.month - 1)
    else:
        fileDiaoDuHuiZongLast = fileDiaoDuHuiZongCur + '\\{}\\{}月调度工单汇总表.xlsx'.format(time_set.year, time_set.month)
        if time_set.month == 12:
            fileDiaoDuHuiZong = fileDiaoDuHuiZongCur + '\\{}\\1月调度工单汇总表.xlsx'.format(time_set.year + 1)
            fileDiaoDuHuiZongDir = fileDiaoDuHuiZongCur + '\\{}'.format(time_set.year + 1)
        else:
            fileDiaoDuHuiZong = fileDiaoDuHuiZongCur + '\\{}\\{}月调度工单汇总表.xlsx'.format(time_set.year, time_set.month + 1)
    logger.debug('日期：{} 本月调度工单汇总表：{}'.format(time_set, fileDiaoDuHuiZong))
    logger.debug('日期：{} 上月调度工单汇总表：{}'.format(time_set, fileDiaoDuHuiZongLast))
    if os.path.exists(fileDiaoDuHuiZongDir) is False:
        os.makedirs(fileDiaoDuHuiZongDir)


def make_save_hui_zong(file_save):
    global bookDiaoDuHuiZong
    global sheetDiaoDuHuiZong
    if os.path.exists(file_save):
        bookDiaoDuHuiZong = openpyxl.load_workbook(file_save, data_only=True)
        sheetDiaoDuHuiZong = bookDiaoDuHuiZong['未解决工单汇总']
    else:
        bookDiaoDuHuiZong = openpyxl.Workbook()  # 创建一个excel文件
        sheetDiaoDuHuiZong = bookDiaoDuHuiZong.active  # 获得一个的工作表
        sheetDiaoDuHuiZong.title = "未解决工单汇总"
        for indexTitle in range(0, len(listTitle)):
            sheetDiaoDuHuiZong.cell(1, indexTitle + 1).value = listTitle[indexTitle]

if isSaveUndetermined != '0':
    make_diao_du_file(timeCur)
    make_save_hui_zong(fileDiaoDuHuiZong)


# 修改特定的承办部门
def xiu_gai_cheng_ban_bu_men(sheet_source, col_title, col_source, col_dest):
    colTitleIndex = get_col_index(sheet_source, col_title)
    maxRowSource = sheet_source.max_row
    for rowSource in range(2, maxRowSource+1):
        cellValueSearch = sheet_source.cell(rowSource, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            cellValueSearch = cellValueSearch.replace(col_source, col_dest)
            sheet_source.cell(rowSource, colTitleIndex).value = cellValueSearch


# 修改承办部门
def do_xiu_gai_cheng_ban_bu_men(sheet_cur):
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '疑难案件', '市民诉求处置中心')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '疑难工单', '市民诉求处置中心')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '应急小分队', '市民诉求处置中心')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '流动人口管理组', '社会治理组')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '送变电公司社区', '送变电社区')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '于管营', '紫汇家园社区')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '徐庄', '紫汇家园社区')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '教委', '教委（双管）')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '食药所', '食药所（双管）')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '综合城管一组', '综合行政执法队')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '综合城管二组', '综合行政执法队')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '综合城管三组', '综合行政执法队')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '综合城管四组', '综合行政执法队')
    xiu_gai_cheng_ban_bu_men(sheet_cur, '承办部门', '综合城管五组', '综合行政执法队')


# 清空固定某一列 （col_index） 的数据（ min_row行 到 max_row行）
def clear_cells(sheet_cur, col_index, min_row, max_row):
    for rowBookCur in range(min_row, max_row+1):
        curCellValue = sheet_cur.cell(rowBookCur, col_index).value
        if curCellValue is not None:
            sheet_cur.cell(rowBookCur, col_index).value = None
        if timeCur.day == 20:
            logger.debug('today is 20,clear col {}'.format(col_index - 1))
            sheet_cur.cell(rowBookCur, col_index - 1).value = None
    keySum = sheet_cur.cell(max_row + 1, col_index).coordinate
    # 求和的开始单元格地址
    sumStart = sheet_cur.cell(min_row, col_index).coordinate
    # 求和的结束单元格地址
    sumEnd = sheet_cur.cell(max_row, col_index).coordinate
    sheet_cur[keySum] = f'=SUM({sumStart}:{sumEnd})'

    keySumCount = sheet_cur.cell(max_row + 1, col_index - 1).coordinate
    # 求和的开始单元格地址
    sumStartCount = sheet_cur.cell(min_row, col_index - 1).coordinate
    # 求和的结束单元格地址
    sumEndCount = sheet_cur.cell(max_row, col_index - 1).coordinate
    sheet_cur[keySumCount] = f'=SUM({sumStartCount}:{sumEndCount})'


# 获取某一列的索引号 col_name 列名
def get_col_index(sheet_source, col_name):
    maxColSheetSource = sheet_source.max_column
    for colSource in range(1, maxColSheetSource+1):
        colCellValue = sheet_source.cell(1, colSource).value
        if colCellValue is not None:
            colCellValue = colCellValue.strip()
            if colCellValue == col_name:
                return colSource
    return None


# 删除某一列
def delete_col_by_title(sheet_delete, col_title):
    indexColDelete = get_col_index(sheet_delete, col_title)
    if indexColDelete is not None and indexColDelete != 0:
        sheet_delete.delete_cols(indexColDelete)


# 保存 当日的未解决工单 到 工单汇总列表
def save_unresolved_to_collect(sheet_hui_zong, sheet_wei, row_index):
    global isSaveUndetermined
    if isSaveUndetermined == '0':
        logger.debug('不保存到未解决汇总列表，行号：{}', row_index)
        return
    maxRowHuiZong = sheet_hui_zong.max_row
    colDiaoDuLastHui = get_col_index(sheet_hui_zong, '上一次调度日期')
    colIdHui = get_col_index(sheet_hui_zong, '工单编号')
    colDiaoDuDate = get_col_index(sheet_hui_zong, '调度日期')
    colDiaoDuLastWei = get_col_index(sheet_wei, '上一次调度日期')
    colIdWei = get_col_index(sheet_wei, '工单编号')
    strToday = '{}-{}-{}'.format(timeCur.year, timeCur.month, timeCur.day)
    valueIdInsert = sheet_wei.cell(row_index, colIdWei).value
    dateDiaoDuLastInsert = sheet_wei.cell(row_index, colDiaoDuLastWei).value
    if dateDiaoDuLastInsert is not None:
        dateDiaoDuLastInsert = '{}'.format(dateDiaoDuLastInsert)
    if valueIdInsert is not None:
        valueIdInsert = valueIdInsert.strip()
    for rowHui in range(maxRowHuiZong, 1, -1):
        valueId = sheet_hui_zong.cell(rowHui, colIdHui).value
        dateDiaoDuLastHui = sheet_hui_zong.cell(rowHui, colDiaoDuLastHui).value
        if valueId is not None:
            valueId = valueId.strip()
        if dateDiaoDuLastHui is not None:
            dateDiaoDuLastHui = '{}'.format(dateDiaoDuLastHui)
        if valueId == valueIdInsert and dateDiaoDuLastInsert == dateDiaoDuLastHui:
            logger.debug('工单编号：{} 上一次调度日期:{} 已存在汇总列表'.format(valueIdInsert, dateDiaoDuLastInsert))
            return
    sheet_hui_zong.cell(maxRowHuiZong + 1, colDiaoDuDate).value = strToday
    for titleSave in listTitle:
        colSrc = get_col_index(sheet_wei, titleSave)
        colDest = get_col_index(sheet_hui_zong, titleSave)
        if colSrc is not None and colDest is not None:
            sheet_hui_zong.cell(maxRowHuiZong + 1, colDest).value = sheet_wei.cell(row_index, colSrc).value


# 根据表内数据 匹配工单数
def xiu_gai_gong_dan_shu_liang(sheet_src, sheet_des, sheet_dest_data, col_search, col_des_w, min_row_des,
                               max_row_des, cols):
    colIndex = get_col_index(sheet_src, '承办部门')
    colOrderId = get_col_index(sheet_src, '工单编号')
    colDiaoDuLast = get_col_index(sheet_src, '上一次调度日期')
    if colDiaoDuLast is None:
        logger.debug('未找到 上一次调度日期 列，统计接单工单'.format(colDiaoDuLast))
    else:
        logger.debug('上一次调度日期 在{}列，统计未解决工单'.format(colDiaoDuLast))
    maxRowSheetSource = sheet_src.max_row
    for rowSource in range(2, maxRowSheetSource+1):
        colCellSourceValue = sheet_src.cell(rowSource, colIndex).value
        valueOrderId = sheet_src.cell(rowSource, colOrderId).value
        if colCellSourceValue is not None:
            colCellSourceValue = colCellSourceValue.strip()
            for rowDes in range(min_row_des, max_row_des+1):
                colCellDesValue = sheet_dest_data.cell(rowDes, col_search).value
                if colCellDesValue is not None:
                    colCellDesValue = colCellDesValue.strip()
                    colCellDesValue = colCellDesValue.replace('\n', '')
                    colCellDesValue = colCellDesValue.replace('\r', '')
                    colCellDesValue = colCellDesValue.replace('\r\n', '')
                    # logger.debug('-{}-{}-'.format(colCellSourceValue, colCellDesValue))
                    if colCellDesValue == colCellSourceValue:
                        logger.debug('-{}-{}-'.format(valueOrderId, colCellDesValue))
                        cols.append(rowSource)
                        colCellDesWriteValue = sheet_dest_data.cell(rowDes, col_des_w).value
                        colDesWriteCountValue = sheet_dest_data.cell(rowDes, col_des_w - 1).value
                        if colCellDesWriteValue is not None:
                            colCellDesWriteValue = colCellDesWriteValue + 1
                        else:
                            colCellDesWriteValue = 1
                        sheet_des.cell(rowDes, col_des_w).value = colCellDesWriteValue
                        sheet_dest_data.cell(rowDes, col_des_w).value = colCellDesWriteValue
                        isAvaliable = True
                        if colDiaoDuLast is not None:
                            valueLastDiaoDu = sheet_src.cell(rowSource, colDiaoDuLast).value
                            if valueLastDiaoDu is not None:
                                timeLastDiaoDu = datetime.datetime.strptime(valueLastDiaoDu, "%Y-%m-%d")
                                if timeLastDiaoDu > timeKaoHe:
                                    isAvaliable = False
                            if isSaveUndetermined != '0':
                                save_unresolved_to_collect(sheetDiaoDuHuiZong, sheet_src, rowSource)
                        if isAvaliable:
                            if colDesWriteCountValue is not None:
                                colDesWriteCountValue = colDesWriteCountValue + 1
                            else:
                                colDesWriteCountValue = 1
                            sheet_des.cell(rowDes, col_des_w - 1).value = colDesWriteCountValue
                            sheet_dest_data.cell(rowDes, col_des_w - 1).value = colDesWriteCountValue


# 生成为解决汇总表的序号
def make_hui_zong_xu_hao(sheet_hui):
    maxRowHui = sheet_hui.max_row
    numXuHao = 1
    colXuHao = get_col_index(sheet_hui, '序号')
    if colXuHao is not None and colXuHao != 0:
        for rowHui in range(2, maxRowHui+1):
            valueXuHao = sheet_hui.cell(rowHui, colXuHao).value
            if valueXuHao is None:
                sheet_hui.cell(rowHui, colXuHao).value = numXuHao
                numXuHao = numXuHao + 1


logger.debug('-今日未解决工单明细.xlsx-')
delRowForToday = []
delRowForTodayDaPing = []
# 打开 今日未解决工单明细.xlsx
if isProcessUnresolved is True:
    bookWorkOrderToday = openpyxl.load_workbook(fileWorkOrderToday, data_only=True)
    sheetWorkOrderToday = bookWorkOrderToday['所有工单明细']
    maxRowWorkOrderToday = sheetWorkOrderToday.max_row
    maxColWorkOrderToday = sheetWorkOrderToday.max_column
    logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderToday, maxColWorkOrderToday))  # 打印表的行列数
    do_xiu_gai_cheng_ban_bu_men(sheetWorkOrderToday)
    # 删除不是未解决的工单
    delRowList = []
    colNotJieJueIndex = get_col_index(sheetWorkOrderToday, '是否解决')
    for rowIsJieJue in range(2, maxRowWorkOrderToday+1):
        collValueCur = sheetWorkOrderToday.cell(rowIsJieJue, colNotJieJueIndex).value
        if collValueCur is not None:
            collValueCur = collValueCur.strip()
            if collValueCur != '未解决':
                delRowList.append(rowIsJieJue)
        else:
            delRowList.append(rowIsJieJue)

    logger.debug('删除的列序号（排序前）：{}'.format(delRowList))
    delRowList.reverse()
    logger.debug('删除的列序号（排序后）：{}'.format(delRowList))
    for colDel in delRowList:
        sheetWorkOrderToday.delete_rows(colDel)


countYesterday = 0
delRowForDispatch = []
delRowForDispatchDaPing = []
if isProcessReceiving is True:
    logger.debug('-派单组-每日台账明细\\全部工单.xlsx-')
    # 派单组上传的今日派单情况
    bookWorkOrderDispatch = openpyxl.load_workbook(fileWorkOrderDispatch, data_only=True)
    # sheetNamesWorkOrderDispatch = bookWorkOrderDispatch.sheetnames
    # # bookWorkOrderDispatch.active
    # logger.debug('Sheet名称：{}'.format(sheetNamesWorkOrderDispatch[1]))  # 打印 第二个Sheet页名称
    # sheetWorkOrderDispatch = bookWorkOrderDispatch[sheetNamesWorkOrderDispatch[1]]
    sheetWorkOrderDispatch = bookWorkOrderDispatch['全部工单']
    maxRowWorkOrderDispatch = sheetWorkOrderDispatch.max_row
    maxColWorkOrderDispatch = sheetWorkOrderDispatch.max_column
    logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderDispatch, maxColWorkOrderDispatch))  # 打印表的行列数

    # colChengBanIndex = get_col_index(sheetWorkOrderDispatch, '承办部门')
    # xiu_gai_cheng_ban_bu_men(sheetWorkOrderDispatch, colChengBanIndex)
    do_xiu_gai_cheng_ban_bu_men(sheetWorkOrderDispatch)
    # bookWorkOrderDispatch.save(fileWorkOrderDispatchOut)

# ----------------------------------------------------------拱辰街道科室接单量及解决情况.xlsx
logger.debug('-拱辰街道科室接单量及解决情况.xlsx-')
# 拱辰街道科室接单量及解决情况
bookWorkOrderKeShi = openpyxl.load_workbook(fileWorkOrderKeShi)
sheetWorkOrderKeShi = bookWorkOrderKeShi.active
bookWorkOrderKeShiData = openpyxl.load_workbook(fileWorkOrderKeShi, data_only=True)
sheetWorkOrderKeShiData = bookWorkOrderKeShiData.active
maxRowWorkOrderCentral = sheetWorkOrderKeShi.max_row
maxColWorkOrderCentral = sheetWorkOrderKeShi.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderCentral, maxColWorkOrderCentral))  # 打印表的行列数

# 修改表格标题
sheetWorkOrderKeShi.cell(1, 1).value = titleWorkOrderKeShi
# 计算昨天的接单量
if sheetWorkOrderKeShiData.cell(43, 5).value is not None:
    countYesterday = countYesterday + sheetWorkOrderKeShiData.cell(43, 5).value
    logger.debug('科室前一天接单量：{}  接单总量：{}'.format(sheetWorkOrderKeShiData.cell(43, 5).value, countYesterday))

# 清空 今日接单量
clear_cells(sheetWorkOrderKeShi, 5, 3, 42)
# 清空 今日未解决工单量（件）
clear_cells(sheetWorkOrderKeShi, 7, 3, 42)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetWorkOrderKeShi, 9, 3, 42)

# 清空 今日接单量
clear_cells(sheetWorkOrderKeShiData, 5, 3, 42)
# 清空 今日未解决工单量（件）
clear_cells(sheetWorkOrderKeShiData, 7, 3, 42)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetWorkOrderKeShiData, 9, 3, 42)

if isProcessUnresolved is True:
    # 修改 今日回访未解决工单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetWorkOrderKeShi, sheetWorkOrderKeShiData, 3, 7, 3, 42,
                               delRowForToday)

if isProcessReceiving is True:
    # 修改 今日接单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetWorkOrderKeShi, sheetWorkOrderKeShiData, 3, 5, 3, 42,
                               delRowForDispatch)

# 保存
bookWorkOrderKeShi.save(fileWorkOrderKeShiOut)

# ----------------------------------------------------------拱辰街道北部片区接单量及解决情况.xlsx
logger.debug('-拱辰街道北部片区接单量及解决情况.xlsx-')
# 拱辰街道北部片区接单量及解决情况
bookWorkOrderNorth = openpyxl.load_workbook(fileWorkOrderNorth)
sheetWorkOrderNorth = bookWorkOrderNorth.active
bookWorkOrderNorthData = openpyxl.load_workbook(fileWorkOrderNorth, data_only=True)
sheetWorkOrderNorthData = bookWorkOrderNorthData.active
maxRowWorkOrderNorth = sheetWorkOrderNorth.max_row
maxColWorkOrderNorth = sheetWorkOrderNorth.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderNorth, maxColWorkOrderNorth))  # 打印表的行列数

# 修改表格标题
sheetWorkOrderNorth.cell(1, 1).value = titleWorkOrderNorth
# 计算昨天的接单量
if sheetWorkOrderNorthData.cell(17, 5).value is not None:
    countYesterday = countYesterday + sheetWorkOrderNorthData.cell(17, 5).value
    logger.debug('北部片区前一天接单量：{}  接单总量：{}'.format(sheetWorkOrderNorthData.cell(17, 5).value, countYesterday))

# 清空 今日接单量-北部片区
clear_cells(sheetWorkOrderNorth, 5, 4, 16)
# 清空 今日未解决工单量（件）-北部片区
clear_cells(sheetWorkOrderNorth, 7, 4, 16)
# 清空 今日渐次升级解决工单量（件）-北部片区
clear_cells(sheetWorkOrderNorth, 9, 4, 16)

# 清空 今日接单量-北部片区
clear_cells(sheetWorkOrderNorthData, 5, 4, 16)
# 清空 今日未解决工单量（件）-北部片区
clear_cells(sheetWorkOrderNorthData, 7, 4, 16)
# 清空 今日渐次升级解决工单量（件）-北部片区
clear_cells(sheetWorkOrderNorthData, 9, 4, 16)

logger.debug('-拱辰街道北部片区接单量及解决情况统计表.xlsx-大屏')
# 拱辰街道北部片区接单量及解决情况统计表
bookWorkOrderNorthDaPing = openpyxl.load_workbook(fileWorkOrderNorthDaPing)
sheetWorkOrderNorthDaPing = bookWorkOrderNorthDaPing.active
bookWorkOrderNorthDaPingData = openpyxl.load_workbook(fileWorkOrderNorthDaPing, data_only=True)
sheetWorkOrderNorthDaPingData = bookWorkOrderNorthDaPingData.active
maxRowWorkOrderNorthDaPing = sheetWorkOrderNorthDaPing.max_row
maxColWorkOrderNorthDaPing = sheetWorkOrderNorthDaPing.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderNorthDaPing, maxColWorkOrderNorthDaPing))  # 打印表的行列数
# 修改表格标题
sheetWorkOrderNorthDaPing.cell(1, 1).value = titleWorkOrderNorth

# 清空 今日接单量
clear_cells(sheetWorkOrderNorthDaPing, 5, 4, 16)
# 清空 今日未解决工单量（件）
clear_cells(sheetWorkOrderNorthDaPing, 7, 4, 16)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetWorkOrderNorthDaPing, 9, 4, 16)

# 清空 今日接单量
clear_cells(sheetWorkOrderNorthDaPingData, 5, 4, 16)
# 清空 今日未解决工单量（件）
clear_cells(sheetWorkOrderNorthDaPingData, 7, 4, 16)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetWorkOrderNorthDaPingData, 9, 4, 16)

if isProcessUnresolved is True:
    # 修改 今日回访未解决工单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetWorkOrderNorth, sheetWorkOrderNorthData, 2, 7, 4, 16,
                               delRowForToday)
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetWorkOrderNorthDaPing, sheetWorkOrderNorthDaPingData, 2, 7, 4,
                               16, delRowForTodayDaPing)
if isProcessReceiving is True:
    # 修改 今日接单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetWorkOrderNorth, sheetWorkOrderNorthData, 2, 5, 4, 16,
                               delRowForDispatch)
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetWorkOrderNorthDaPing, sheetWorkOrderNorthDaPingData, 2, 5,
                               4, 16, delRowForDispatchDaPing)

# 保存
bookWorkOrderNorth.save(fileWorkOrderNorthOut)
bookWorkOrderNorthDaPing.save(fileWorkOrderNorthDaPingOut)

# ----------------------------------------------------------拱辰街道西南片区接单量及解决情况.xlsx
logger.debug('-拱辰街道西南片区接单量及解决情况.xlsx-')
# 拱辰街道西南片区接单量及解决情况
bookWorkOrderSouthWest = openpyxl.load_workbook(fileWorkOrderSouthWest)
sheetSouthWest = bookWorkOrderSouthWest.active
bookWorkOrderSouthWestData = openpyxl.load_workbook(fileWorkOrderSouthWest, data_only=True)
sheetSouthWestData = bookWorkOrderSouthWestData.active
maxRowWorkOrderSouthWest = sheetSouthWest.max_row
maxColWorkOrderSouthWest = sheetSouthWest.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderSouthWest, maxColWorkOrderSouthWest))  # 打印表的行列数

# 修改表格标题
sheetSouthWest.cell(1, 1).value = titleWorkOrderSouthWest
# 计算昨天的接单量
if sheetSouthWestData.cell(19, 5).value is not None:
    countYesterday = countYesterday + sheetSouthWestData.cell(19, 5).value
    logger.debug('西南片区前一天接单量：{}  接单总量：{}'.format(sheetSouthWestData.cell(19, 5).value, countYesterday))

# 清空 今日接单量
clear_cells(sheetSouthWest, 5, 4, 18)
# 清空 今日未解决工单量（件）
clear_cells(sheetSouthWest, 7, 4, 18)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetSouthWest, 9, 4, 18)

# 清空 今日接单量
clear_cells(sheetSouthWestData, 5, 4, 18)
# 清空 今日未解决工单量（件）
clear_cells(sheetSouthWestData, 7, 4, 18)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetSouthWestData, 9, 4, 18)


logger.debug('-拱辰街道西南片区接单量及解决情况统计表.xlsx-大屏')
# 拱辰街道西南片区接单量及解决情况统计表
bookWorkOrderSouthWestDaPing = openpyxl.load_workbook(fileWorkOrderSouthWestDaPing)
sheetSouthWestDaPing = bookWorkOrderSouthWestDaPing.active
bookWorkOrderSouthWestDaPingData = openpyxl.load_workbook(fileWorkOrderSouthWestDaPing, data_only=True)
sheetSouthWestDaPingData = bookWorkOrderSouthWestDaPingData.active
maxRowWorkOrderSouthWestDaPing = sheetSouthWestDaPing.max_row
maxColWorkOrderSouthWestDaPing = sheetSouthWestDaPing.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderSouthWestDaPing, maxColWorkOrderSouthWestDaPing))  # 打印表的行列数
# 修改表格标题
sheetSouthWestDaPing.cell(1, 1).value = titleWorkOrderSouthWest

# 清空 今日接单量
clear_cells(sheetSouthWestDaPing, 5, 4, 18)
# 清空 今日未解决工单量（件）
clear_cells(sheetSouthWestDaPing, 7, 4, 18)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetSouthWestDaPing, 9, 4, 18)

# 清空 今日接单量
clear_cells(sheetSouthWestDaPingData, 5, 4, 18)
# 清空 今日未解决工单量（件）
clear_cells(sheetSouthWestDaPingData, 7, 4, 18)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetSouthWestDaPingData, 9, 4, 18)

if isProcessUnresolved is True:
    # 修改 今日回访未解决工单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetSouthWest, sheetSouthWestData, 2, 7, 4, 18, delRowForToday)
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetSouthWestDaPing, sheetSouthWestDaPingData, 2, 7, 4, 18,
                               delRowForTodayDaPing)

if isProcessReceiving is True:
    # 修改 今日接单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetSouthWest, sheetSouthWestData, 2, 5, 4, 18,
                               delRowForDispatch)
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetSouthWestDaPing, sheetSouthWestDaPingData, 2, 5, 4, 18,
                               delRowForDispatchDaPing)
# 保存
bookWorkOrderSouthWest.save(fileWorkOrderSouthWestOut)
bookWorkOrderSouthWestDaPing.save(fileWorkOrderSouthWestDaPingOut)

# ----------------------------------------------------------拱辰街道东部片区接单量及解决情况.xlsx
logger.debug('-拱辰街道东部片区接单量及解决情况.xlsx-')
# 拱辰街道东部片区接单量及解决情况
bookWorkOrderEast = openpyxl.load_workbook(fileWorkOrderEast)
sheetWorkOrderEast = bookWorkOrderEast.active
bookWorkOrderEastData = openpyxl.load_workbook(fileWorkOrderEast, data_only=True)
sheetWorkOrderEastData = bookWorkOrderEastData.active
maxRowWorkOrderEast = sheetWorkOrderEast.max_row
maxColWorkOrderEast = sheetWorkOrderEast.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderEast, maxColWorkOrderEast))  # 打印表的行列数

# 修改表格标题
sheetWorkOrderEast.cell(1, 1).value = titleWorkOrderEast
# 计算昨天的接单量
if sheetWorkOrderEastData.cell(20, 5).value is not None:
    countYesterday = countYesterday + sheetWorkOrderEastData.cell(20, 5).value
    logger.debug('东部片区前一天接单量：{}  接单总量：{}'.format(sheetWorkOrderEastData.cell(20, 5).value, countYesterday))

# 清空 今日接单量-东部片区
clear_cells(sheetWorkOrderEast, 5, 4, 19)
# 清空 今日未解决工单量（件）-东部片区
clear_cells(sheetWorkOrderEast, 7, 4, 19)
# 清空 今日渐次升级解决工单量（件）-东部片区
clear_cells(sheetWorkOrderEast, 9, 4, 19)

# 清空 今日接单量-东部片区
clear_cells(sheetWorkOrderEastData, 5, 4, 19)
# 清空 今日未解决工单量（件）-东部片区
clear_cells(sheetWorkOrderEastData, 7, 4, 19)
# 清空 今日渐次升级解决工单量（件）-东部片区
clear_cells(sheetWorkOrderEastData, 9, 4, 19)

logger.debug('-拱辰街道东部片区接单量及解决情况统计表.xlsx-大屏')
# 拱辰街道东部片区接单量及解决情况统计表
bookWorkOrderEastDaPing = openpyxl.load_workbook(fileWorkOrderEastDaPing)
sheetWorkOrderEastDaPing = bookWorkOrderEastDaPing.active
bookWorkOrderEastDaPingData = openpyxl.load_workbook(fileWorkOrderEastDaPing, data_only=True)
sheetWorkOrderEastDaPingData = bookWorkOrderEastDaPingData.active
maxRowWorkOrderEastDaPing = sheetWorkOrderEastDaPing.max_row
maxColWorkOrderEastDaPing = sheetWorkOrderEastDaPing.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderEastDaPing, maxColWorkOrderEastDaPing))  # 打印表的行列数

# 修改表格标题
sheetWorkOrderEastDaPing.cell(1, 1).value = titleWorkOrderEast
logger.debug('{}'.format(titleWorkOrderEast))
# 清空 今日接单量
clear_cells(sheetWorkOrderEastDaPing, 5, 4, 19)
# 清空 今日未解决工单量（件）
clear_cells(sheetWorkOrderEastDaPing, 7, 4, 19)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetWorkOrderEastDaPing, 9, 4, 19)

# 清空 今日接单量
clear_cells(sheetWorkOrderEastDaPingData, 5, 4, 19)
# 清空 今日未解决工单量（件）
clear_cells(sheetWorkOrderEastDaPingData, 7, 4, 19)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetWorkOrderEastDaPingData, 9, 4, 19)

if isProcessUnresolved is True:
    # 修改 今日回访未解决工单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetWorkOrderEast, sheetWorkOrderEastData, 2, 7, 4, 19,
                               delRowForToday)
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetWorkOrderEastDaPing, sheetWorkOrderEastDaPingData, 2, 7, 4,
                               19, delRowForTodayDaPing)

if isProcessReceiving is True:
    # 修改 今日接单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetWorkOrderEast, sheetWorkOrderEastData, 2, 5, 4, 19,
                               delRowForDispatch)
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetWorkOrderEastDaPing, sheetWorkOrderEastDaPingData, 2, 5, 4,
                               19, delRowForDispatchDaPing)

bookWorkOrderEast.save(fileWorkOrderEastOut)
bookWorkOrderEastDaPing.save(fileWorkOrderEastDaPingOut)

# ----------------------------------------------------------拱辰街道中部片区接单量及解决情况.xlsx
logger.debug('-拱辰街道中部片区接单量及解决情况.xlsx-')
# 拱辰街道中部片区接单量及解决情况
bookWorkOrderCentral = openpyxl.load_workbook(fileWorkOrderCentral)
sheetCentral = bookWorkOrderCentral.active
bookWorkOrderCentralData = openpyxl.load_workbook(fileWorkOrderCentral, data_only=True)
sheetCentralData = bookWorkOrderCentralData.active
maxRowWorkOrderCentral = sheetCentral.max_row
maxColWorkOrderCentral = sheetCentral.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderCentral, maxColWorkOrderCentral))  # 打印表的行列数

# 修改表格标题
sheetCentral.cell(1, 1).value = titleWorkOrderCentral
# 计算昨天的接单量
if sheetCentralData.cell(16, 5).value is not None:
    countYesterday = countYesterday + sheetCentralData.cell(16, 5).value
    logger.debug('中部片区前一天接单量：{}  接单总量：{}'.format(sheetCentralData.cell(16, 5).value, countYesterday))

# 清空 今日接单量
clear_cells(sheetCentral, 5, 4, 15)
# 清空 今日未解决工单量（件）
clear_cells(sheetCentral, 7, 4, 15)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetCentral, 9, 4, 15)

# 清空 今日接单量
clear_cells(sheetCentralData, 5, 4, 15)
# 清空 今日未解决工单量（件）
clear_cells(sheetCentralData, 7, 4, 15)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetCentralData, 9, 4, 15)

logger.debug('-拱辰街道中部片区接单量及解决情况统计表.xlsx-大屏')
# 拱辰街道中部片区接单量及解决情况统计表
bookWorkOrderCentralDaPing = openpyxl.load_workbook(fileWorkOrderCentralDaPing)
sheetCentralDaPing = bookWorkOrderCentralDaPing.active
bookWorkOrderCentralDaPingData = openpyxl.load_workbook(fileWorkOrderCentralDaPing, data_only=True)
sheetCentralDaPingData = bookWorkOrderCentralDaPingData.active
maxRowWorkOrderCentralDaPing = sheetCentralDaPing.max_row
maxColWorkOrderCentralDaPing = sheetCentralDaPing.max_column
logger.debug('行数：{}  列数：{}'.format(maxRowWorkOrderCentralDaPing, maxColWorkOrderCentralDaPing))  # 打印表的行列数
# 修改表格标题
sheetCentralDaPing.cell(1, 1).value = titleWorkOrderCentral

# 清空 今日接单量
clear_cells(sheetCentralDaPing, 5, 4, 15)
# 清空 今日未解决工单量（件）
clear_cells(sheetCentralDaPing, 7, 4, 15)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetCentralDaPing, 9, 4, 15)

# 清空 今日接单量
clear_cells(sheetCentralDaPingData, 5, 4, 15)
# 清空 今日未解决工单量（件）
clear_cells(sheetCentralDaPingData, 7, 4, 15)
# 清空 今日渐次升级解决工单量（件）
clear_cells(sheetCentralDaPingData, 9, 4, 15)

if isProcessUnresolved is True:
    # 修改 今日回访未解决工单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetCentral, sheetCentralData, 2, 7, 4, 15, delRowForToday)
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderToday, sheetCentralDaPing, sheetCentralDaPingData, 2, 7, 4, 15,
                               delRowForTodayDaPing)

if isProcessReceiving is True:
    # 修改 今日接单量
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetCentral, sheetCentralData, 2, 5, 4, 15, delRowForDispatch)
    xiu_gai_gong_dan_shu_liang(sheetWorkOrderDispatch, sheetCentralDaPing, sheetCentralDaPingData, 2, 5, 4, 15,
                               delRowForDispatchDaPing)
# 保存
bookWorkOrderCentral.save(fileWorkOrderCentralOut)
bookWorkOrderCentralDaPing.save(fileWorkOrderCentralDaPingOut)

if isProcessReceiving is True:
    # 保存 今日接单量 未处理的数据
    logger.debug('今日接单-删除的数据行号（排序前）：{} '.format(delRowForDispatch))
    delRowForDispatch.sort(reverse=True)
    logger.debug('今日接单-删除的数据行号（排序后）：{} '.format(delRowForDispatch))

    for colDel in delRowForDispatch:
        sheetWorkOrderDispatch.delete_rows(colDel)
    maxRowWorkOrderDispatchOutNo = sheetWorkOrderDispatch.max_row
    if maxRowWorkOrderDispatchOutNo > 1:
        if os.path.exists(dirPathWeiChuLi):
            logger.debug('今日接单量-未处理工单保存路径：{}'.format(dirPathWeiChuLi))
        else:
            os.makedirs(dirPathWeiChuLi)
        bookWorkOrderDispatch.save(fileWorkOrderDispatchOutNo)
    else:
        logger.debug('今日接单量 已全部处理')

if isProcessUnresolved is True:
    # 保存 今日回访未解决工单量 未处理的数据
    logger.debug(delRowForToday)
    delRowForToday.sort(reverse=True)
    logger.debug(delRowForToday)

    for colDel in delRowForToday:
        sheetWorkOrderToday.delete_rows(colDel)
    maxRowWorkOrderTodayOutNo = sheetWorkOrderToday.max_row
    if maxRowWorkOrderTodayOutNo > 1:
        if os.path.exists(dirPathWeiChuLi):
            logger.debug('未解决工单-未处理工单保存路径：{]'.format(dirPathWeiChuLi))
        else:
            os.makedirs(dirPathWeiChuLi)
        bookWorkOrderToday.save(fileWorkOrderTodayOutNo)
    else:
        logger.debug('今日回访未解决工单量 已全部处理')

fileResultContent = ''
countYiQing = 0
if isProcessReceiving is True:
    logger.debug('-派单组-每日台账明细\\全部工单.xlsx-')
    logger.debug('接单量文件：{}'.format(fileWorkOrderDispatch))
    fileResultContent = fileResultContent + "\r\n今日工单情况\r\n{}月{}日9时".format(timeCur.month, timeCur.day - 1)
    fileResultContent = fileResultContent + '至{}月{}日9时，'.format(timeCur.month, timeCur.day)
    bookTodayMustDo = openpyxl.load_workbook(fileWorkOrderDispatch, data_only=True)
    # sheetNamesTodayMustDo = bookTodayMustDo.sheetnames
    # # bookWorkOrderDispatch.active
    # logger.debug('Sheet名称：{}'.format(sheetNamesTodayMustDo[1]))  # 打印 第二个Sheet页名称
    # sheetTodayMustDo = bookTodayMustDo[sheetNamesTodayMustDo[1]]
    sheetTodayMustDo = bookTodayMustDo['全部工单']
    maxRowTodayMustDo = sheetTodayMustDo.max_row
    maxColTodayMustDo = sheetTodayMustDo.max_column
    logger.debug('行数：{}  列数：{}'.format(maxRowTodayMustDo, maxColTodayMustDo))  # 打印表的行列数

    # 修改承办部门
    xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '疑难案件', '市民诉求处置中心')
    xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '疑难工单', '市民诉求处置中心')
    xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '应急小分队', '市民诉求处置中心')
    xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '流动人口管理组', '社会治理组')
    xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
    # do_xiu_gai_cheng_ban_bu_men(sheetTodayMustDo)

    # 计算 双满工单数量 解决基本满意数量 解决不满意数量
    colIndexChengBan = get_col_index(sheetTodayMustDo, '承办部门')
    colIndexJieDan = get_col_index(sheetTodayMustDo, '接单部门')
    colIndexBiaoQian = get_col_index(sheetTodayMustDo, '标签')

    # 社区（村）
    numSheQuCun = 0
    keyListSheQuCun = []
    tinyDictSheQuCun = {}
    # 拱辰街道机关科室
    numJiGuanKeShi = 0
    keyListJiGuanKeShi = []
    tinyDictJiGuanKeShi = {}
    logger.debug('承办部门：{}    接单部门：{}'.format(colIndexChengBan, colIndexJieDan))
    for rowSource in range(2, maxRowTodayMustDo + 1):
        cellJieDan = sheetTodayMustDo.cell(rowSource, colIndexJieDan).value
        cellChengBan = sheetTodayMustDo.cell(rowSource, colIndexChengBan).value
        colBiaoQian = sheetTodayMustDo.cell(rowSource, colIndexBiaoQian).value
        colBiaoQian = '{}'.format(colBiaoQian)
        if colBiaoQian is not None and colBiaoQian == '1':
            countYiQing = countYiQing + 1
        isFindChengGuan = cellChengBan.find('综合城管')
        if isFindChengGuan == 0:
            cellChengBan = '综合行政执法队'
        if cellJieDan is not None:
            cellJieDan = cellJieDan.strip()
            if cellChengBan is not None:
                cellChengBan = cellChengBan.strip()
                if cellJieDan == '拱辰街道机关科室':
                    numJiGuanKeShi = numJiGuanKeShi + 1
                    if cellChengBan in keyListJiGuanKeShi:
                        tinyDictJiGuanKeShi[cellChengBan] = tinyDictJiGuanKeShi[cellChengBan] + 1
                    else:
                        keyListJiGuanKeShi.append(cellChengBan)
                        tinyDictJiGuanKeShi[cellChengBan] = 1
                else:
                    if cellJieDan == '社区（村）':
                        numSheQuCun = numSheQuCun + 1
                        if cellChengBan in keyListSheQuCun:
                            tinyDictSheQuCun[cellChengBan] = tinyDictSheQuCun[cellChengBan] + 1
                        else:
                            keyListSheQuCun.append(cellChengBan)
                            tinyDictSheQuCun[cellChengBan] = 1

    numAllJieDan = numJiGuanKeShi + numSheQuCun
    if numAllJieDan > 0:
        fileResultContent = fileResultContent + '共接收工单{}件（详见附件1）'.format(numAllJieDan)
        numAdd = numAllJieDan - countYesterday
        if numAllJieDan - countYesterday > 0:
            fileResultContent = fileResultContent + '，比前一日增加{}件'.format(numAllJieDan - countYesterday)
        elif numAllJieDan - countYesterday < 0:
            fileResultContent = fileResultContent + '，比前一日减少{}件'.format(countYesterday - numAllJieDan)
        fileResultContent = fileResultContent + '，其中日常诉求{}件，涉疫诉求{}件'.format(numAllJieDan-countYiQing, countYiQing)
        fileResultContent = fileResultContent + '，主要反映问题包括：\r\n'

        if numJiGuanKeShi > 0:
            fileResultContent = fileResultContent + '涉及机关科室{}件（'.format(numJiGuanKeShi)
            tinyDictJiGuanKeShi = sorted(tinyDictJiGuanKeShi.items(), key=lambda kv: kv[1], reverse=True)
            logger.debug('{}'.format(tinyDictJiGuanKeShi))
            numLast = 0
            numCount = 0
            numIndex = 0
            for keyNoKeShi in tinyDictJiGuanKeShi:
                numIndex = numIndex + 1
                if numLast == 0:
                    numLast = keyNoKeShi[1]
                else:
                    if numLast != keyNoKeShi[1]:
                        if numCount > 1:
                            fileResultContent = fileResultContent + '各{}件，'.format(numLast)
                        else:
                            fileResultContent = fileResultContent + '{}件，'.format(numLast)
                        numCount = 0
                if numLast == keyNoKeShi[1]:
                    if numCount == 0:
                        fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
                    else:
                        fileResultContent = fileResultContent + '、{}'.format(keyNoKeShi[0])
                else:
                    fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
                numCount = numCount + 1
                numLast = keyNoKeShi[1]
            if numCount > 1:
                fileResultContent = fileResultContent + '各{}件'.format(numLast)
            else:
                fileResultContent = fileResultContent + '{}件'.format(numLast)
            fileResultContent = fileResultContent + '）。'

        if numSheQuCun > 0:
            fileResultContent = fileResultContent + '\n涉及社区（村）{}件（'.format(numSheQuCun)
            tinyDictSheQuCun = sorted(tinyDictSheQuCun.items(), key=lambda kv: kv[1], reverse=True)
            # logger.debug('{}'.format(tinyDictSheQuCun))
            numLastSheQu = 0
            numIndexSheQu = 0
            numCountSheQu = 0
            for keyNoSheQu in tinyDictSheQuCun:
                numIndexSheQu = numIndexSheQu + 1
                if numLastSheQu == 0:
                    numLastSheQu = keyNoSheQu[1]
                else:
                    if numLastSheQu != keyNoSheQu[1]:
                        if numCountSheQu > 1:
                            fileResultContent = fileResultContent + '各{}件，'.format(numLastSheQu)
                        else:
                            fileResultContent = fileResultContent + '{}件，'.format(numLastSheQu)
                        numCountSheQu = 0
                if numLastSheQu == keyNoSheQu[1]:
                    if numCountSheQu == 0:
                        fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
                    else:
                        fileResultContent = fileResultContent + '、{}'.format(keyNoSheQu[0])
                else:
                    fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
                numCountSheQu = numCountSheQu + 1
                numLastSheQu = keyNoSheQu[1]
            if numCountSheQu > 1:
                fileResultContent = fileResultContent + '各{}件'.format(numLastSheQu)
            else:
                fileResultContent = fileResultContent + '{}件'.format(numLastSheQu)
            fileResultContent = fileResultContent + '）。'
        fileResultContent = fileResultContent + '截止16时，已办结X件，办理中X件（详见附件2）。'
    logger.debug('{}'.format(fileResultContent))

logger.debug('-每日必办文案-工单回访情况.xlsx-')
# 打开今日未解决工单明细
if isProcessUnresolved is True:
    fileResultSave = dirPath + '\\每日必办文案.txt'
    bookWeiJieJue = openpyxl.load_workbook(fileWorkOrderToday, data_only=True)
    sheetWeiJieJue = bookWeiJieJue['所有工单明细']
    maxRowWeiJieJue = sheetWeiJieJue.max_row
    maxColWeiJieJue = sheetWeiJieJue.max_column
    logger.debug('行数：{}  列数：{}'.format(maxRowWeiJieJue, maxColWeiJieJue))  # 打印表的行列数

    # 获取有效回访件数
    colIndex = get_col_index(sheetWeiJieJue, '回访状态')
    maxRowSource = sheetWeiJieJue.max_row
    numYouXiao = 0
    for rowSource in range(2, maxRowSource + 1):
        cellValue = sheetWeiJieJue.cell(rowSource, colIndex).value
        if cellValue is not None:
            cellValue = cellValue.strip()
            if cellValue == '有效回访':
                numYouXiao = numYouXiao + 1

    # 计算 双满工单数量 解决基本满意数量 解决不满意数量
    colIndexJieJueManYi = get_col_index(sheetWeiJieJue, '是否解决\n是否满意')
    colIndexChengBan = get_col_index(sheetWeiJieJue, '承办部门')
    colIndexTitle = get_col_index(sheetWeiJieJue, '标题')
    colIndexArea = get_col_index(sheetWeiJieJue, '片区')

    # 解决 满意
    numDoubleOk = 0
    # 解决 基本满意
    numJiBenManYi = 0
    keyListJiBenManYi = []
    tinyDictJiBenManYiNum = {}
    tinyDictJiBenManYiTitle = {}
    # 解决 不满意
    numJieJueBuManYi = 0
    keyListBuManYi = []
    tinyDictBuManYiNum = {}
    tinyDictBuManYiTitle = {}
    # 未解决 科室
    numNoKeShi = 0
    keyListNoKeShi = []
    dictNoKeShiNum = {}
    # 未解决 社区/村
    numNoSheQu = 0
    keyListNoSheQu = []
    dictNoSheQuNum = {}

    for rowSource in range(2, maxRowSource + 1):
        cellValue = sheetWeiJieJue.cell(rowSource, colIndexJieJueManYi).value
        cellChengBan = sheetWeiJieJue.cell(rowSource, colIndexChengBan).value
        cellValueYouXiao = sheetWeiJieJue.cell(rowSource, colIndex).value
        if cellValueYouXiao != '有效回访':
            continue
        if cellChengBan is not None:
            isFindChengGuan = cellChengBan.find('综合城管')
            if isFindChengGuan == 0:
                cellChengBan = '综合行政执法队'
            cellArea = sheetWeiJieJue.cell(rowSource, colIndexArea).value
            cellTitle = sheetWeiJieJue.cell(rowSource, colIndexTitle).value
            if cellValue is not None:
                cellValue = cellValue.strip()
                if cellChengBan is not None:
                    cellChengBan = cellChengBan.strip()
                    if cellTitle is not None:
                        cellTitle = cellTitle.strip()
                        # logger.debug('{}'.format(cellValue))
                        if cellValue == '解决\n满意' or cellValue == '解决\n非常满意':
                            numDoubleOk = numDoubleOk + 1
                            # logger.debug('{}-------true'.format(cellValue))
                            continue
                        if cellValue == '解决\n基本满意':
                            numJiBenManYi = numJiBenManYi + 1
                            if cellChengBan in keyListJiBenManYi:
                                tinyDictJiBenManYiNum[cellChengBan] = tinyDictJiBenManYiNum[cellChengBan] + 1
                                cellTitleOld = tinyDictJiBenManYiTitle[cellChengBan]
                                tinyDictJiBenManYiTitle[cellChengBan] = cellTitleOld + '、' + cellTitle
                            else:
                                keyListJiBenManYi.append(cellChengBan)
                                tinyDictJiBenManYiNum[cellChengBan] = 1
                                tinyDictJiBenManYiTitle[cellChengBan] = cellTitle
                            continue
                        if cellValue == '解决\n不满意' or cellValue == '解决\n非常不满意':
                            numJieJueBuManYi = numJieJueBuManYi + 1
                            if cellChengBan in keyListBuManYi:
                                tinyDictBuManYiNum[cellChengBan] = tinyDictBuManYiNum[cellChengBan] + 1
                                cellTitleOld = tinyDictBuManYiTitle[cellChengBan]
                                tinyDictBuManYiTitle[cellChengBan] = cellTitleOld + '、' + cellTitle
                            else:
                                keyListBuManYi.append(cellChengBan)
                                tinyDictBuManYiNum[cellChengBan] = 1
                                tinyDictBuManYiTitle[cellChengBan] = cellTitle
                        else:
                            # logger.debug('-{}-'.format(cellChengBan))
                            if cellArea is not None:
                                cellArea = cellArea.strip()
                                cellArea = cellArea.replace(' ', '')
                                if cellArea == '科室':
                                    numNoKeShi = numNoKeShi + 1
                                    if cellChengBan in keyListNoKeShi:
                                        dictNoKeShiNum[cellChengBan] = dictNoKeShiNum[cellChengBan] + 1
                                    else:
                                        keyListNoKeShi.append(cellChengBan)
                                        dictNoKeShiNum[cellChengBan] = 1
                                    logger.debug('numNoKeShi：{} {}：{}'.format(numNoKeShi, cellChengBan,
                                                                       dictNoKeShiNum[cellChengBan]))
                                else:
                                    numNoSheQu = numNoSheQu + 1
                                    if cellChengBan in keyListNoSheQu:
                                        dictNoSheQuNum[cellChengBan] = dictNoSheQuNum[cellChengBan] + 1
                                    else:
                                        keyListNoSheQu.append(cellChengBan)
                                        dictNoSheQuNum[cellChengBan] = 1

    for cellChengBan in keyListJiBenManYi:
        if tinyDictJiBenManYiNum[cellChengBan] > 1:
            tinyDictJiBenManYiTitle[cellChengBan] = tinyDictJiBenManYiTitle[cellChengBan] + '各1件'
    for cellChengBan in keyListBuManYi:
        if tinyDictBuManYiNum[cellChengBan] > 1:
            tinyDictBuManYiTitle[cellChengBan] = tinyDictBuManYiTitle[cellChengBan] + '各1件'
    fileResultContent = fileResultContent + '\r\n工单回访情况\r\n'

    # logger.debug('区级有效回访工单{}件，'.format(numYouXiao))
    # logger.debug('其中双满工单{}件；'.format(numDoubleOk))
    # logger.debug('解决基本满意工单{}件'.format(numJiBenManYi))
    # logger.debug('解决基本满意工单{}件'.format(numJieJueBuManYi))
    # logger.debug('未解决工单{}件'.format(numNoKeShi + numNoSheQu))

    fileResultContent = fileResultContent + '\r\n工单回访情况\r\n'
    numWuCha = numYouXiao - (numDoubleOk + numJiBenManYi + numJieJueBuManYi + numNoKeShi + numNoSheQu)
    if numWuCha != 0:
        fileResultContent = fileResultContent + '\r\n请注意：有{}工单未匹配到承办部门，请仔细核对！！！！！\r\n\r\n'.format(numWuCha)
        fileResultContent = fileResultContent + '区级有效回访工单{}件，'.format(numYouXiao)
        fileResultContent = fileResultContent + '\r\n其中双满工单{}件；'.format(numDoubleOk)
        fileResultContent = fileResultContent + '\r\n解决基本满意工单{}件'.format(numJiBenManYi)
        fileResultContent = fileResultContent + '\r\n解决基本满意工单{}件'.format(numJieJueBuManYi)
        fileResultContent = fileResultContent + '\r\n未解决工单{}件\r\n'.format(numNoKeShi + numNoSheQu)

    fileResultContent = fileResultContent + "{}月{}日9时".format(timeCur.month, timeCur.day - 1)
    fileResultContent = fileResultContent + '至{}月{}日9时，'.format(timeCur.month, timeCur.day)
    fileResultContent = fileResultContent + '区级有效回访工单{}件，'.format(numYouXiao)
    fileResultContent = fileResultContent + '其中双满工单{}件；'.format(numDoubleOk)

    if numJiBenManYi > 0:
        fileResultContent = fileResultContent + '解决基本满意工单{}件（'.format(numJiBenManYi)
        tinyDictJiBenManYiNum = sorted(tinyDictJiBenManYiNum.items(), key=lambda kv: kv[1], reverse=True)
        logger.debug('解决基本满意工单{}件：{}'.format(numJiBenManYi, tinyDictJiBenManYiNum))
        for key in tinyDictJiBenManYiNum:
            value = '{}{}件：{}'.format(key[0], key[1], tinyDictJiBenManYiTitle[key[0]])
            if key != tinyDictJiBenManYiNum[0]:
                value = '；' + value
            fileResultContent = fileResultContent + value
            # logger.debug('{}'.format(value))
        fileResultContent = fileResultContent + '）。'

    if numJieJueBuManYi > 0:
        fileResultContent = fileResultContent + '解决不满意工单{}件（'.format(numJieJueBuManYi)
        tinyDictBuManYiNum = sorted(tinyDictBuManYiNum.items(), key=lambda kv: kv[1], reverse=True)
        logger.debug('解决不满意工单{}件：{}'.format(numJieJueBuManYi, tinyDictBuManYiNum))
        for keyBuManYi in tinyDictBuManYiNum:
            value = '{}{}件：{}；'.format(keyBuManYi[0], keyBuManYi[1], tinyDictBuManYiTitle[keyBuManYi[0]])
            fileResultContent = fileResultContent + value
            # logger.debug('{}'.format(value))
        fileResultContent = fileResultContent + '）。'

    if numNoKeShi + numNoSheQu > 0:
        fileResultContent = fileResultContent + '\n未解决工单{}件:其中难以解决工单X件：XXXX件：' \
                                                'XXXX。（详见附件4）。\n'.format(numNoKeShi + numNoSheQu)
        fileResultContent = fileResultContent + '经督办申请二次回访0件，剩余未解决工单{}件：'.format(numNoKeShi + numNoSheQu)
        if numNoKeShi > 0:
            fileResultContent = fileResultContent + '其中涉及机关科室{}件（'.format(numNoKeShi, numNoKeShi)
            dictNoKeShiNum = sorted(dictNoKeShiNum.items(), key=lambda kv: kv[1], reverse=True)
            # logger.debug('{}'.format(dictNoKeShiNum))
            numLast = 0
            numCount = 0
            numIndex = 0
            for keyNoKeShi in dictNoKeShiNum:
                numIndex = numIndex + 1
                if numLast == 0:
                    numLast = keyNoKeShi[1]
                else:
                    if numLast != keyNoKeShi[1]:
                        if numCount > 1:
                            fileResultContent = fileResultContent + '各{}件，'.format(numLast)
                        else:
                            fileResultContent = fileResultContent + '{}件，'.format(numLast)
                        numCount = 0
                if numLast == keyNoKeShi[1]:
                    if numCount == 0:
                        fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
                    else:
                        fileResultContent = fileResultContent + '、{}'.format(keyNoKeShi[0])
                else:
                    fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
                numCount = numCount + 1
                numLast = keyNoKeShi[1]
            if numCount > 1:
                fileResultContent = fileResultContent + '各{}件'.format(numLast)
            else:
                fileResultContent = fileResultContent + '{}件'.format(numLast)
            fileResultContent = fileResultContent + '）。'
        if numNoSheQu > 0:
            fileResultContent = fileResultContent + '涉及社区（村）{}件（'.format(numNoSheQu)
            dictNoSheQuNum = sorted(dictNoSheQuNum.items(), key=lambda kv: kv[1], reverse=True)
            # logger.debug('{}'.format(dictNoSheQuNum))
            numLastSheQu = 0
            numIndexSheQu = 0
            numCountSheQu = 0
            for keyNoSheQu in dictNoSheQuNum:
                numIndexSheQu = numIndexSheQu + 1
                if numLastSheQu == 0:
                    numLastSheQu = keyNoSheQu[1]
                else:
                    if numLastSheQu != keyNoSheQu[1]:
                        if numCountSheQu > 1:
                            fileResultContent = fileResultContent + '各{}件，'.format(numLastSheQu)
                        else:
                            fileResultContent = fileResultContent + '{}件，'.format(numLastSheQu)
                        numCountSheQu = 0
                if numLastSheQu == keyNoSheQu[1]:
                    if numCountSheQu == 0:
                        fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
                    else:
                        fileResultContent = fileResultContent + '、{}'.format(keyNoSheQu[0])
                else:
                    fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
                numCountSheQu = numCountSheQu + 1
                numLastSheQu = keyNoSheQu[1]
            if numCountSheQu > 1:
                fileResultContent = fileResultContent + '各{}件'.format(numLastSheQu)
            else:
                fileResultContent = fileResultContent + '{}件'.format(numLastSheQu)
            fileResultContent = fileResultContent + '）。详见附件3。'
    logger.debug('fileResultSave:{}'.format(fileResultSave))
    logger.debug('今日工单情况 和 工单回访情况:{}'.format(fileResultContent))
    with open(fileResultSave, "w") as f:
        f.write(fileResultContent)
if isSaveUndetermined != '0':
    make_hui_zong_xu_hao(sheetDiaoDuHuiZong)
    bookDiaoDuHuiZong.save(fileDiaoDuHuiZong)


